<?php

class Admin_Table_Auction_Auction extends Apk_Db_Table {

    protected $_name = 'auction';
    protected $_primary = 'ID';
    public $_ERROR = "";

    /*
     * region_dir => dir_region
     * district_dir => dir_district
     * town_dir => dir_town
     * town_type_dir => dir_town_type
     * 
     * decode => ELT
     * */

    public function GetError() {
        return $this->_ERROR;
    }

    public function getDataList($page = 1, $sort = null, $page_count = null, $filters = array()) {
        $sel_inner = $this->getAdapter()->select()
                ->from($this->_name, array($this->_name . '.id', 'start_date' => "start_date"))
                ->joinLeft('auction_address', $this->_name . ".id_address = auction_address.id", array('place' => "auction_address.place"))
                ->joinLeft('dir_region', "dir_region.id = auction_address.id_region", array('region' => "dir_region.name"))
                ->joinLeft('dir_district', "dir_district.id = auction_address.id_district", array('district' => 'dir_district.name', 'district_type' => 'dir_district.type'))
                ->joinLeft('dir_town', "dir_town.id = auction_address.id_town", array('town' => "dir_town.name"))
                ->joinLeft('dir_town_type', "dir_town.town_type = dir_town_type.id", array('town_type' => "dir_town_type.shortname"))
                ->joinLeft(array('auction_lots_status'), "auction_lots_status.id = " . $this->_name . ".id_status", array('status' => 'name'))
                ->where($this->_name . ".is_ghost = ?", 0)
                ->where($this->_name . ".is_removed = ?", 0);

        if (count($filters) > 0)
            foreach ($filters as $k => $v) {
                switch ($k) {
                    case 'by_date':
                        if ($this->checkDateFormat($v))
                            $sel_inner->where("start_date >= '" . date("Y-m-d H:i:s", strtotime($v . ' 00:00:00')) . "' and start_date <= '" . date("Y-m-d H:i:s", strtotime($v . ' 23:59:59')) . "'");
                        break;
                    case 'from_date':
                        if ($this->checkDateFormat($v))
                            $sel_inner->where("start_date > '" . date("Y-m-d H:i:s", strtotime($v . ' 23:59:59')) . "'");
                        break;
                    case 'to_date':
                        if ($this->checkDateFormat($v))
                            $sel_inner->where("start_date < '" . date("Y-m-d H:i:s", strtotime($v . ' 00:00:00')) . "'");
                        break;
                    case 'by_region':
                        if (intval($v))
                            $sel_inner->where("dir_region.id = ?", intval($v));
                        break;
                    case 'by_district':
                        if (intval($v))
                            $sel_inner->where("dir_district.id = ?", intval($v));
                        break;
                    case 'by_town':
                        if (intval($v))
                            $sel_inner->where("dir_town.id = ?", intval($v));
                        break;
                    case 'by_status':
                        if (intval($v) > 0)
                            $sel_inner->where($this->_name . ".id_status = ?", intval($v));
                        break;
                    case 'koatuu':
                        $koatuu_offset_mask = $this->KOATUUOffsetMask($v);
                        if (!empty($koatuu_offset_mask)) {                            
                            $sel_inner->where("RPad(RPad(LPad(" . $this->_name . "." . $k . ", 10, 0), $koatuu_offset_mask, 0), 10, 0) = " . $v);
                        }
                        break;
                    //default:
                    //    if(intval($v) > 0)
                    //        $sel_inner->where($this->_name.'.'.$k.' = ?', $v);
                }
            }

        $pieces = preg_split("/\s/u", $sort);
        switch ($pieces[0]) {
            case 'id':
                $sel_inner->order($this->_name . ".id " . $pieces[1]);
                break;
            case 'start_date':
                $sel_inner->order("start_date " . $pieces[1]);
                break;
            case 'region':
                $sel_inner->order("dir_region.name " . $pieces[1]);
                break;
            case 'district':
                $sel_inner->order("dir_district.name " . $pieces[1]);
                break;
            case 'town':
                $sel_inner->order("dir_town.name " . $pieces[1]);
                break;
            case 'status':
                $sel_inner->order($this->_name . ".id_status " . $pieces[1]);
                break;
            default:
                $sel_inner->order(array("start_date DESC"));
                break;
        }
        //echo $sel_inner; die();
        $sel = $this->getAdapter()->select()
                ->from($sel_inner, array('id', 'start_date', 'region', 'district' => "IF(district_type='М', concat('м. ', district), district)", 'town' => "concat(town_type,' ',town)", "place", "status")); //district_type
        //echo $sel; die();
        return $this->setDecorator('simpleDecorator')->getPager($sel, $page, array('perPage' => $page_count));
    }

    public function getDataRow($id) {
        $data = array();
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array($this->_name . ".id", 'start_date', $this->_name . ".id_address", $this->_name . ".id_executant", $this->_name . '.add_date', $this->_name . '.modify_date', $this->_name . '.reject_reason', $this->_name . ".id_status"))
                ->joinLeft('auction_address', $this->_name . ".id_address = auction_address.id", array('auction_address.id_region', 'auction_address.id_district', 'auction_address.id_town', 'auction_address.place'))
                ->joinLeft(array('add_account' => "accounts"), "add_account.id = " . $this->_name . ".add_user_id", array('add_user' => "concat(add_account.last_name,' ',add_account.first_name,' ',add_account.patronymic_name)"))
                ->joinLeft(array('modify_account' => "accounts"), "modify_account.id = " . $this->_name . ".modify_user_id", array('modify_user' => "concat(modify_account.last_name,' ',modify_account.first_name,' ',modify_account.patronymic_name)"))
                ->joinLeft('auction_executant', $this->_name . ".id_executant = auction_executant.id", array('executant_name' => 'auction_executant.name'/* , 'auction_executant.id_region', 'auction_executant.id_district', 'auction_executant.id_town', 'auction_executant.place' */))
                ->where($this->_name . ".id = ?", $id);

        $data = $this->setDecorator('simpleDecorator')->getList($sel)->toArray();
        if (!empty($data))
            $data = $data[0];
        return $data;
    }

    public function insert(array $data, $user_id, $user_koatuu = null) {
        $data['add_date'] = new Zend_Db_Expr("now()");
        $data['start_date'] = new Zend_Db_Expr("now()");
        $data['add_user_id'] = $user_id;
        $data['id_status'] = '0';
        $data['koatuu'] = $user_koatuu;
        parent::insert($data);
        return $this->getAdapter()->lastInsertId($this->_name);
    }

    public function update(array $data, $id, $user_id, $role = null) {
        try {
            $where = '';
            if(isset($role) && $role == 14){ //AuctionEditor
                if($data['id_status'] == 3 )$where = ' and id_status = 1';                
            }
            if(isset($role) && $role == 15){ //AuctionCheck
                if($data['id_status'] == 2 )$where = ' and id_status = 1';
            }
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            return parent::update($data, 'id = ' . $id . $where);
        } catch (Exception $e) {
            return false;
        }
    }

    public function remove(array $items, $user_id) {
        $table_lots = new Admin_Table_Auction_Lots();

        foreach ($items as $id) {
            $published = $this->getAuctionLotsCount($id, 'true');
            //remove only if no published lots in this auction (id_status != 2)
            if ((intval($published['lots_count'])) == 0) {
                $data = array();
                $data['modify_user_id'] = $user_id;
                $data['modify_date'] = new Zend_Db_Expr("now()");
                $data['is_removed'] = 1;

                //remove only if not published(id_status != 2)
                if (parent::update($data, "id = " . $id . " and id_status != 2")) {

                    $this->removeAddress($id);

                    $ids = $table_lots->getAuctionLotsIDs($id);
                    $data = array();

                    foreach ($ids as $lot_id) {
                        $data[] = $lot_id['id'];
                    }

                    $table_lots->remove($data, $user_id);
                }
            }
        }

        return true;
    }

    private function removeAddress($id_auction) {
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array('id_address'))
                ->where("id = ?", $id_auction);

        $result = $this->getList($sel)->toArray();

        if (!empty($result)) {
            $this->getAdapter()->update('auction_address', array('is_removed' => 1), "id = " . $result[0]['id_address']);
        }
    }

    public function addressGetRegions($region_id = null) {
        $sel = $this->getAdapter()->select()
                ->from('dir_region', array('id', 'name'))
                ->order('name');

        $result = $this->setDecorator('selectDecorator')->getList($sel)->toArray();

        if (!empty($result) && isset($region_id))
            foreach ($result as $key => $value)
                if ($value['id'] == $region_id)
                    $result[$key]['__attributes']['active'] = 1;

        return $result;
    }

    public function addressGetDistricts($region_id = null, $district_id = null) {
        $result = array();

        if (isset($region_id)) {
            $sel = $this->getAdapter()->select()
                    ->from('dir_district', array('id', 'name' => "IF(type='М', concat('м. ', name), name)"))
                    ->where('region_id = ?', $region_id)
                    ->order("case when substr(name, 1, 3) = 'м. ' then substr(name, 4) else name end");

            $result = $this->setDecorator('selectDecorator')->getList($sel)->toArray();

            if (!empty($result) && isset($district_id))
                foreach ($result as $key => $value)
                    if ($value['id'] == $district_id)
                        $result[$key]['__attributes']['active'] = 1;
        }

        return $result;
    }

    public function addressGetTowns($region_id = null, $district_id = null, $town_id = null) {
        $result = array();

        if (isset($region_id) && isset($district_id)) {
            $sel_inner = $this->getAdapter()->select()
                    ->from('dir_town', array('dir_town.id', 'name' => "dir_town.name", 'type' => "dir_town_type.shortname"))
                    ->join('dir_town_type', "dir_town.town_type = dir_town_type.id", array())
                    ->where('dir_town.region_id = ?', $region_id)
                    ->where('dir_town.district_id = ?', $district_id)
                    ->order('name');

            $sel = $this->getAdapter()->select()
                    ->from($sel_inner, array('id', 'name' => "concat(type,' ',name)"));

            $result = $this->setDecorator('selectDecorator')->getList($sel)->toArray();

            if (!empty($result) && isset($town_id))
                foreach ($result as $key => $value)
                    if ($value['id'] == $town_id)
                        $result[$key]['__attributes']['active'] = 1;
        }

        return $result;
    }

    public function checkStatus($items, $user_id, $id_status=1) {
        $notInStatus = array('1' => '1, 2');
        foreach ($items as $k => $id) {
            parent::update(array('id_status' => $id_status, 'id_status_reason' => 0, 'modify_user_id' => $user_id), 'id_status not in(' . $notInStatus[$id_status] . ') and id = ' . $id);
        }
        return true;
    }
    
    public function changeStatus($items, $user_id, $id_status=1) {
        $notInStatus = array('1' => '1, 2');
        foreach ($items as $k => $id) {
            parent::update(array('id_status' => $id_status, 'id_status_reason' => 0, 'modify_user_id' => $user_id), 'id_status not in(' . $notInStatus[$id_status] . ') and id = ' . $id);
        }
        return true;
    }
    
    public function getStatus($id_auction) {
        $data = array();
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array($this->_name.".id_status"))
                ->where($this->_name.".id = ?", $id_auction);
        $data = $this->getList($sel)->toArray();
        if (!empty($data))
            $data = $data[0]["id_status"];
        return $data;
    }
/*
    public function updateByChecker(array $data, $id, $user_id) {
        try {
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            return parent::update($data, 'id = ' . $id . ' and id_status = 1');
        } catch (Exception $e) {
            echo $e->getMessage();            
            return false;
        }
    }

    public function updateByEditor(array $data, $id, $user_id) {
        try {
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            return parent::update($data, 'id = ' . $id . ' and id_status = 1');
        } catch (Exception $e) {
            echo $e->getMessage();
            return false;
        }
    }
*/
    
    
    public function updateAuction(array $data, $id, $user_id, $user_koatuu = null) {
        try {

            $address_data = array(
                'id_region'   => @$data['id_region'],
                'id_district' => @$data['id_district'],
                'id_town'     => @$data['id_town'],
                'place'       => @$data['place']
            );
            $auction_data = array(
                'start_date'   => date("Y-m-d H:i:s", strtotime(@$data['start_date'] . ':00')),
                'id_address'   => @$data['id_address'],
                'id_executant' => @$data['id_executant'],
                'koatuu'       => $user_koatuu
            );
            unset($data);
            if (empty($auction_data['id_address'])) {
                $this->getAdapter()->insert("auction_address", $address_data);
                $auction_data['id_address'] = $this->getAdapter()->lastInsertId("address");
            } else
                $this->getAdapter()->update("auction_address", $address_data, "id = " . $auction_data['id_address']);

            return $this->update($auction_data, $id, $user_id);
        } catch (Exception $e) {
            $this->_ERROR = "Помилка бази даних";
            return false;
        }
    }

    public function getAuctionInfo($id) {
        $data = array();
        $fields = array(
            $this->_name . ".id", 'start_date',
            $this->_name . '.add_date',
            $this->_name . '.modify_date',
            $this->_name . ".id_executant",
            $this->_name . ".id_status",
            $this->_name . ".reject_reason");
        $sel_inner = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->joinLeft('auction_address', $this->_name . ".id_address = auction_address.id", array('place'))
                ->joinLeft(array('add_account' => "accounts"), "add_account.id = " . $this->_name . ".add_user_id", array('add_user' => "concat(add_account.last_name,' ',add_account.first_name,' ',add_account.patronymic_name)"))
                ->joinLeft(array('modify_account' => "accounts"), "modify_account.id = " . $this->_name . ".modify_user_id", array('modify_user' => "concat(modify_account.last_name,' ',modify_account.first_name,' ',modify_account.patronymic_name)"))
                ->joinLeft('dir_region', "dir_region.id = auction_address.id_region", array('region' => "dir_region.name"))
                ->joinLeft('dir_district', "dir_district.id = auction_address.id_district", array('district' => 'dir_district.name', 'district_type' => 'dir_district.type'))
                ->joinLeft('dir_town', "dir_town.id = auction_address.id_town", array('town' => "dir_town.name"))
                ->joinLeft('dir_town_type', "dir_town.town_type = dir_town_type.id", array('town_type' => "dir_town_type.shortname"))
                ->joinLeft('auction_executant', $this->_name . ".id_executant = auction_executant.id", array('executant_name' => 'auction_executant.name', 'executant_address' => 'auction_executant.address', 
                                                                                'executant_person_position' => 'auction_executant.person_position', 'executant_person_fio' => 'auction_executant.person_fio',  'executant_person_phone' => 'auction_executant.person_phone',
                                                                                'executant_requisites_recipient' => 'auction_executant.requisites_recipient', 'executant_requisites_recipient_code' => 'auction_executant.requisites_recipient_code',  'executant_requisites_bank' => 'auction_executant.requisites_bank',
                                                                                'executant_requisites_bank_code' => 'auction_executant.requisites_bank_code', 'executant_requisites_score' => 'auction_executant.requisites_score'))
                ->joinLeft(array('executant_dir_region'=>'dir_region'), "executant_dir_region.id = auction_executant.id_region", array('executant_region' => "dir_region.name"))
                ->joinLeft(array('executant_dir_district'=>'dir_district'), "executant_dir_district.id = auction_executant.id_district", array('executant_district' => 'dir_district.name', 'executant_district_type' => 'dir_district.type'))
                ->joinLeft(array('executant_dir_town'=>'dir_town'), "executant_dir_town.id = auction_executant.id_town", array('executant_town' => "dir_town.name"))
                ->joinLeft(array('executant_dir_town_type'=>'dir_town_type'), "executant_dir_town.town_type = executant_dir_town_type.id", array('executant_town_type' => "dir_town_type.shortname"))
                ->joinLeft(array('auction_lots_status'), "auction_lots_status.id = " . $this->_name . ".id_status", array('status' => 'name'))
                ->where($this->_name . ".id = ?", $id);

        $sel = $this->getAdapter()->select()
                ->from($sel_inner, 
                        array('id', 'start_date', 'add_date', 'modify_date', 
                              'place', 'add_user', 'modify_user', 'region', 
                              'district' => "IF(district_type='М', concat('м. ', district), district)", 
                              'town' => "concat(town_type,' ',town)", 
                              'id_status', 'status', 'reject_reason', 
                              'executant_name', 'executant_region',
                              'executant_district' => "IF(executant_district_type='М', concat('м. ', executant_district), executant_district)", 
                              'executant_town' => "concat(executant_town_type,' ',executant_town)",
                              'executant_address', 'executant_person_position',
                              'executant_person_fio', 'executant_person_phone',
                              'executant_requisites_recipient','executant_requisites_recipient_code',
                              'executant_requisites_bank',
                              'executant_requisites_bank_code','executant_requisites_score'
                            ));

        $data = $this->setDecorator("simpleDecorator")->getList($sel)->toArray();
        if (!empty($data))
            $data = $data[0];
        return $data;
    }

    public function getAuctionLotsCount($id, $onlyPublished = false) {
        $sel = $this->getAdapter()->select()
                ->from("auction_lots", array('lots_count' => "count(*)"))
                ->where("id_auction = ?", $id)
                ->where("is_removed = ?", 0)
                ->where("is_ghost = ?", 0);
        if ($onlyPublished)
            $sel->where("id_status = ?", 2);
        $res = $this->getList($sel)->toArray();
        return $res[0];
    }

    private function checkDateFormat($str_date) {
        if (preg_match("/[0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}/u", $str_date))
            return true;
        else
            return false;
    }

    /**
     * Get list of Auction Executant
     *
     * @param integer $id_executant
     * @return array
     */
    public function getAuctionExecutantList($executant = null) {
        $result = array();
        $sel = $this->getAdapter()->select()
                ->from('auction_executant', array('id', 'name'))
                ->where("is_removed = ?", 0)
                ->where("is_ghost = ?", 0)
                ->order('name');

        $result = $this->getList($sel)->toArray();

        if (!empty($result) && isset($executant))
            foreach ($result as $key => $value)
                if ($value['id'] == $executant)
                    $result[$key]['__attributes']['active'] = 1;

        return $result;
    }
    
    
// параметр $role устанавливает оператора для выборки списка возможных статусов
    public function GetStatusListForChange($role, $active_status = null) {
        
        if ($active_status != null && $active_status == '') $active_status = 0;
        $toStatus = array('0' => array(                         // 0 - райзем
                                    '0'  => array('1'),
                                    '1'  => array('3','12'),
                                    '2'  => array('11','12','13','14','15'), 
                                    '3'  => array('1','12'),
                                    '11' => array(),
                                    '12' => array(),
                                    '13' => array(),
                                    '14' => array(),
                                    '15' => array('11','12','13','14'),
                                    '16' => array()),
                          '1' => array(                         // 1 -облзем
                                    '0'  => array(),
                                    '1'  => array('2', '3'),
                                    '2'  => array('15'),
                                    '3'  => array(),
                                    '11' => array('16'),
                                    '12' => array('16'),
                                    '13' => array('16'),
                                    '14' => array('16'),
                                    '15' => array(),
                                    '16' => array())
                        );    
        $sel = $this->getAdapter()->select()
                ->from('auction_lots_status', array('id', 'name'))                
                ->order('id');
        //if(isset($status_id)) $sel->where('id > ?',$start);
        $result = $this->getList($sel)->toArray();//->setDecorator('selectDecorator')
        if (!empty($result) && isset($active_status))
            foreach ($result as $key => $value){
                //if ($value['id'] == $active_status)
                if(!in_array($value['id'], $toStatus[$role][$active_status]))
                    $result[$key]['__attributes']['disable'] = 1;
                if ($value['id'] == $active_status)
                    $result[$key]['__attributes']['active'] = 1;
            }

        return $result;
    }    

    /*     * ******************
     * **  Decorators  ***
     * ******************* */

    public function simpleDecorator($key, $value) {
        //$result[$key]='';
        switch ($key) {
            case 'ins_date':
            case 'end_date':
            case 'deadline_date':
            case 'add_date':
            case 'modify_date':
            case 'price_date':
                if ($value == "" || $value === null) {
                    $result[$key] = '';
                } else {
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y');
                }
                break;
            case 'start_date':
                if ($value == "" || $value === null) {
                    $result[$key] = '';
                } else {
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y H:i');
                }
                break;
            case 'public_date':
            case 'archive_date':
                $result[$key]['__attributes'] = $this->timestampToArray($value);
                break;
            case 'town':
            case 'region':
            case 'district':
            case 'executant_town':
            case 'executant_region':
            case 'executant_district':
                $result[$key] = $this->convertGeomNameStr($value);
                break;
            case 'status':
                if($value == '')
                    $result[$key] = 'Щойно створений';
                else $result[$key] = $value;
                break;
            default:
                $result[$key] = stripslashes($value);
                break;
        }
        return $result;
    }

    public function selectDecorator($key, $value) {
        switch ($key) {
            case 'name':
                $result[$key] = $this->convertGeomNameStr($value);
                break;
            case 'start_date':
                if ($value == "" || $value === null) {
                    $result[$key] = '';
                } else {
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y H:i');
                }
                break;
            default:
                $result[$key] = stripslashes($value);
                break;
        }
        return $result;
    }

    private function convertGeomNameStr($str, $encoding="UTF-8", $delim=".") {
        $str_prefix = "";
        $str_parts = explode(".", $str);
        if (!empty($str_parts)) {
            $str = "";
            foreach ($str_parts as $part) {
                if (!(mb_strlen(trim($part), $encoding) > 2))
                    $str_prefix .= $part . ".";
                else {
                    if ($str != "")
                        $str .= ". ";
                    $str .= $part;
                }
            }
            if ($str_prefix != "")
                $str_prefix .= " ";
        }
        return $str_prefix . mb_convert_case(mb_strtolower(trim($str)), MB_CASE_TITLE, $encoding);
    }

}